package cn.jsxz.common.utils.ExcelUtil;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

/**
 * excel 导出
 */
public class MergeCell {
    /**
     * 合并单元格
     *
     * @param args
     * @throws Exception
     * @Title:MergeCell
     * @Description:
     * @Date:2015年11月4日 下午2:36:46
     * @return: void
     */
    @SuppressWarnings({"resource", "deprecation"})
    public static void main(String[] args) throws Exception {
        //创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = workbook.createSheet("学生表");
        // sheet.setColumnWidth(0, 20 * 256);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //创建单元格
        HSSFRow row = sheet.createRow(0);  //创建第一行
        HSSFCell c0 = row.createCell(0);    //第一列
        c0.setCellValue(new HSSFRichTextString("学号"));
        HSSFCell c1 = row.createCell(1);
        c1.setCellValue(new HSSFRichTextString("姓名"));
        HSSFCell c2 = row.createCell(2);
        c2.setCellValue(new HSSFRichTextString("性别"));
        HSSFCell c3 = row.createCell(3);
        c3.setCellValue(new HSSFRichTextString("年龄"));
        HSSFCell c4 = row.createCell(4);
        c4.setCellValue(new HSSFRichTextString("2015年分数"));
        HSSFCell c5 = row.createCell(7);
        c5.setCellValue(new HSSFRichTextString("2014年分数"));

        HSSFRow row1 = sheet.createRow(1);  //第二行
        HSSFCell c6 = row1.createCell(4);
        c6.setCellValue(new HSSFRichTextString("语文"));
        HSSFCell c7 = row1.createCell(5);
        c7.setCellValue(new HSSFRichTextString("数学"));
        HSSFCell c8 = row1.createCell(6);
        c8.setCellValue(new HSSFRichTextString("外语"));

        HSSFCell c9 = row1.createCell(7);
        c9.setCellValue(new HSSFRichTextString("语文"));
        HSSFCell c10 = row1.createCell(8);
        c10.setCellValue(new HSSFRichTextString("数学"));
        HSSFCell c11 = row1.createCell(9);
        c11.setCellValue(new HSSFRichTextString("外语"));

        for (int i = 2; i <= 10; i++) {
            HSSFRow row8 = sheet.createRow(i);
            for (int j = 0; j <= 9; j++) {
                HSSFCell c66 = row8.createCell(j);
                c66.setCellValue(new HSSFRichTextString("科技"));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(2, 10, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 6));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9));

        //给全部有数据的单元格设置样式
        int rowNum = sheet.getLastRowNum();//获得总行数
        int coloumNum = sheet.getRow(1).getPhysicalNumberOfCells();
        for (int i = 0; i <= rowNum; i++) {//行
            for (int j = 0; j <= 9; j++) {//列
                sheet.setColumnWidth(j, 22 * 256);
                HSSFRow tempRow = sheet.getRow(i);
                if (i == 0) {
                    tempRow.setHeightInPoints(20);
                } else if (i == 5) {
                    tempRow.setHeightInPoints(20);
                } else {
                    tempRow.setHeightInPoints(20);
                }
                HSSFCell cell_temp = tempRow.getCell(j);
                if (cell_temp == null) {
                    cell_temp = tempRow.createCell(j);
                    cell_temp.setCellStyle(style);
                } else {
                    cell_temp.setCellStyle(style);
                }
            }
        }

//这个就是合并单元格
//参数说明：1：开始行 2：结束行  3：开始列 4：结束列
//比如我要合并 第二行到第四行的    第六列到第八列     sheet.addMergedRegion(new CellRangeAddress(1,3,5,7));
        sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 1, (short) 0));
        sheet.addMergedRegion(new CellRangeAddress(0, (short) 1, 1, (short) 1));
        sheet.addMergedRegion(new CellRangeAddress(0, (short) 2, 1, (short) 2));
        sheet.addMergedRegion(new CellRangeAddress(0, (short) 3, 1, (short) 3));
        sheet.addMergedRegion(new CellRangeAddress(0, (short) 4, 0, (short) 6));
        sheet.addMergedRegion(new CellRangeAddress(0, (short) 7, 0, (short) 9));
        FileOutputStream stream = new FileOutputStream("d:/student.xls");
        workbook.write(stream);


        //exportdjs(10,5,new ArrayList<Map>());
    }

    public static void exportdjs(OutputStream os, Integer rows, Integer cells, List<Map> list) throws Exception {
        //创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = workbook.createSheet("地接社统计表");
        // sheet.setColumnWidth(0, 20 * 256);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建标题

        //创建单元格
        HSSFRow row = sheet.createRow(0);  //创建第一行
        HSSFCell c0 = row.createCell(0);    //第一列
        c0.setCellValue(new HSSFRichTextString("地接社"));
        HSSFCell c1 = row.createCell(1);
        c1.setCellValue(new HSSFRichTextString("境内旅客"));
        HSSFCell c2 = row.createCell(2);
        c2.setCellValue(new HSSFRichTextString("境外旅客"));
        HSSFCell c3 = row.createCell(3);
        c3.setCellValue(new HSSFRichTextString("旅客总数"));
        HSSFCell c4 = row.createCell(4);
        c4.setCellValue(new HSSFRichTextString("查询日期"));

        //填充数据
        for (int i = 0; i < list.size(); i++) {//行
            HSSFRow row8 = sheet.createRow(i + 1);
            HSSFCell c66 = row8.createCell(0);
            c66.setCellValue(new HSSFRichTextString((String) list.get(i).get("unitName")));
            HSSFCell c661 = row8.createCell(1);
            c661.setCellValue(new HSSFRichTextString(list.get(i).get("jingneicount").toString()));
            HSSFCell c662 = row8.createCell(2);
            c662.setCellValue(new HSSFRichTextString(list.get(i).get("jingwaicount").toString()));
            HSSFCell c663 = row8.createCell(3);
            c663.setCellValue(new HSSFRichTextString(list.get(i).get("zonggongcount").toString()));
            HSSFCell c664 = row8.createCell(4);
            c664.setCellValue(new HSSFRichTextString((String) list.get(i).get("chaxuntime")));
        }

        //给全部有数据的单元格设置样式
        int rowNum = sheet.getLastRowNum();//获得总行数
        int coloumNum = sheet.getRow(1).getPhysicalNumberOfCells();
        for (int i = 0; i <= rowNum; i++) {//行
            for (int j = 0; j <= 9; j++) {//列
                sheet.setColumnWidth(j, 22 * 256);
                HSSFRow tempRow = sheet.getRow(i);
                if (i == 0) {
                    tempRow.setHeightInPoints(20);
                } else if (i == 5) {
                    tempRow.setHeightInPoints(20);
                } else {
                    tempRow.setHeightInPoints(20);
                }
                HSSFCell cell_temp = tempRow.getCell(j);
                if (cell_temp == null) {
                    cell_temp = tempRow.createCell(j);
                    cell_temp.setCellStyle(style);
                } else {
                    cell_temp.setCellStyle(style);
                }
            }
        }
        workbook.write(os);
        workbook.close();
        os.flush();
        os.close();
    }


    /**
     * 组团社
     *
     * @param os
     * @param list
     * @throws Exception
     */
    public static void exportzts(OutputStream os, List<Map> list) throws Exception {
        //创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = workbook.createSheet("组团社统计表");
        // sheet.setColumnWidth(0, 20 * 256);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建标题

        //创建单元格
        HSSFRow row = sheet.createRow(0);  //创建第一行
        HSSFCell c0 = row.createCell(0);    //第一列
        c0.setCellValue(new HSSFRichTextString("组团社"));
        HSSFCell c1 = row.createCell(1);
        c1.setCellValue(new HSSFRichTextString("旅客总数"));
        HSSFCell c2 = row.createCell(2);
        c2.setCellValue(new HSSFRichTextString("查询时间"));
        //填充数据
        for (int i = 0; i < list.size(); i++) {//行
            HSSFRow row8 = sheet.createRow(i + 1);
            HSSFCell c66 = row8.createCell(0);
            c66.setCellValue(new HSSFRichTextString((String) list.get(i).get("ztagency")));
            HSSFCell c661 = row8.createCell(1);
            c661.setCellValue(new HSSFRichTextString(list.get(i).get("num").toString()));
            HSSFCell c662 = row8.createCell(2);
            c662.setCellValue(new HSSFRichTextString(list.get(i).get("chaxuntime").toString()));
        }

        //给全部有数据的单元格设置样式
        int rowNum = sheet.getLastRowNum();//获得总行数
        int coloumNum = sheet.getRow(1).getPhysicalNumberOfCells();
        for (int i = 0; i <= rowNum; i++) {//行
            for (int j = 0; j <= 9; j++) {//列
                sheet.setColumnWidth(j, 22 * 256);
                HSSFRow tempRow = sheet.getRow(i);
                if (i == 0) {
                    tempRow.setHeightInPoints(20);
                } else if (i == 5) {
                    tempRow.setHeightInPoints(20);
                } else {
                    tempRow.setHeightInPoints(20);
                }
                HSSFCell cell_temp = tempRow.getCell(j);
                if (cell_temp == null) {
                    cell_temp = tempRow.createCell(j);
                    cell_temp.setCellStyle(style);
                } else {
                    cell_temp.setCellStyle(style);
                }
            }
        }
        workbook.write(os);
        workbook.close();
        os.flush();
        os.close();
    }


    /**
     * 自驾游
     *
     * @param os
     * @param list
     * @throws Exception
     */
    public static void exportzjy(OutputStream os, List<Map> list) throws Exception {
        //创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = workbook.createSheet("自驾游统计表");
        // sheet.setColumnWidth(0, 20 * 256);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建标题

        //创建单元格
        HSSFRow row = sheet.createRow(0);  //创建第一行
        HSSFCell c0 = row.createCell(0);    //第一列
        c0.setCellValue(new HSSFRichTextString("地接社"));
        HSSFCell c1 = row.createCell(1);
        c1.setCellValue(new HSSFRichTextString("旅客总数"));
        HSSFCell c2 = row.createCell(2);
        c2.setCellValue(new HSSFRichTextString("查询时间"));
        //填充数据
        for (int i = 0; i < list.size(); i++) {//行
            HSSFRow row8 = sheet.createRow(i + 1);
            HSSFCell c66 = row8.createCell(0);
            c66.setCellValue(new HSSFRichTextString((String) list.get(i).get("unitName")));
            HSSFCell c661 = row8.createCell(1);
            c661.setCellValue(new HSSFRichTextString(list.get(i).get("num").toString()));
            HSSFCell c662 = row8.createCell(2);
            c662.setCellValue(new HSSFRichTextString(list.get(i).get("chaxuntime").toString()));
        }

        //给全部有数据的单元格设置样式
        int rowNum = sheet.getLastRowNum();//获得总行数
        int coloumNum = sheet.getRow(1).getPhysicalNumberOfCells();
        for (int i = 0; i <= rowNum; i++) {//行
            for (int j = 0; j <= 9; j++) {//列
                sheet.setColumnWidth(j, 22 * 256);
                HSSFRow tempRow = sheet.getRow(i);
                if (i == 0) {
                    tempRow.setHeightInPoints(20);
                } else if (i == 5) {
                    tempRow.setHeightInPoints(20);
                } else {
                    tempRow.setHeightInPoints(20);
                }
                HSSFCell cell_temp = tempRow.getCell(j);
                if (cell_temp == null) {
                    cell_temp = tempRow.createCell(j);
                    cell_temp.setCellStyle(style);
                } else {
                    cell_temp.setCellStyle(style);
                }
            }
        }
        workbook.write(os);
        workbook.close();
        os.flush();
        os.close();
    }


    /**
     * 季度导出
     *
     * @param os
     * @param list
     * @throws Exception
     */
    public static void exportjd(OutputStream os, List<Map> list) throws Exception {
        //创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = workbook.createSheet("季度统计表");
        // sheet.setColumnWidth(0, 20 * 256);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建标题

        //创建单元格
        HSSFRow row = sheet.createRow(0);  //创建第一行
        HSSFCell c0 = row.createCell(0);    //第一列
        c0.setCellValue(new HSSFRichTextString("地接社"));
        HSSFCell c1 = row.createCell(1);
        c1.setCellValue(new HSSFRichTextString("总人数"));
        HSSFCell c2 = row.createCell(2);
        c2.setCellValue(new HSSFRichTextString("境内"));
        HSSFCell c3 = row.createCell(3);
        c3.setCellValue(new HSSFRichTextString("境外"));
        HSSFCell c4 = row.createCell(4);
        c4.setCellValue(new HSSFRichTextString("奖励金额"));
        HSSFCell c5 = row.createCell(5);
        c5.setCellValue(new HSSFRichTextString("查询时间"));

        //填充数据
        for (int i = 0; i < list.size(); i++) {//行
            HSSFRow row8 = sheet.createRow(i + 1);
            HSSFCell c66 = row8.createCell(0);
            c66.setCellValue(new HSSFRichTextString((String) list.get(i).get("unitName")));
            HSSFCell c661 = row8.createCell(1);
            c661.setCellValue(new HSSFRichTextString(list.get(i).get("zonggongcount").toString()));
            HSSFCell c662 = row8.createCell(2);
            c662.setCellValue(new HSSFRichTextString(list.get(i).get("jingneicount").toString()));
            HSSFCell c663 = row8.createCell(3);
            c663.setCellValue(new HSSFRichTextString(list.get(i).get("jingwaicount").toString()));
            HSSFCell c664 = row8.createCell(4);
            c664.setCellValue(new HSSFRichTextString(list.get(i).get("totalMoneys").toString()));
            HSSFCell c665 = row8.createCell(5);
            c665.setCellValue(new HSSFRichTextString(list.get(i).get("chaxuntime").toString()));
        }

        //给全部有数据的单元格设置样式
        int rowNum = sheet.getLastRowNum();//获得总行数
        int coloumNum = sheet.getRow(1).getPhysicalNumberOfCells();
        for (int i = 0; i <= rowNum; i++) {//行
            for (int j = 0; j <= 9; j++) {//列
                sheet.setColumnWidth(j, 22 * 256);
                HSSFRow tempRow = sheet.getRow(i);
                if (i == 0) {
                    tempRow.setHeightInPoints(20);
                } else if (i == 5) {
                    tempRow.setHeightInPoints(20);
                } else {
                    tempRow.setHeightInPoints(20);
                }
                HSSFCell cell_temp = tempRow.getCell(j);
                if (cell_temp == null) {
                    cell_temp = tempRow.createCell(j);
                    cell_temp.setCellStyle(style);
                } else {
                    cell_temp.setCellStyle(style);
                }
            }
        }
        workbook.write(os);
        workbook.close();
        os.flush();
        os.close();
    }


    /**
     * 季度详情导出
     *
     * @param os
     * @param list
     * @throws Exception
     */
    public static void exportjddetail(OutputStream os, List<Map> list) throws Exception {
        //创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = workbook.createSheet("季度明细统计表");
        // sheet.setColumnWidth(0, 20 * 256);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建标题

        //创建单元格
        HSSFRow row = sheet.createRow(0);  //创建第一行
        HSSFCell c0 = row.createCell(0);    //第一列
        c0.setCellValue(new HSSFRichTextString("地接社"));
        HSSFCell c1 = row.createCell(1);
        c1.setCellValue(new HSSFRichTextString("组团社"));
        HSSFCell c2 = row.createCell(2);
        c2.setCellValue(new HSSFRichTextString("客源地"));
        HSSFCell c3 = row.createCell(3);
        c3.setCellValue(new HSSFRichTextString("在徐时间"));
        HSSFCell c4 = row.createCell(4);
        c4.setCellValue(new HSSFRichTextString("总人数"));
        HSSFCell c5 = row.createCell(5);
        c5.setCellValue(new HSSFRichTextString("境内"));
        HSSFCell c6 = row.createCell(6);
        c6.setCellValue(new HSSFRichTextString("奖励金额"));
        HSSFCell c7 = row.createCell(7);
        c7.setCellValue(new HSSFRichTextString("奖励组成"));
        HSSFCell c9 = row.createCell(9);
        c9.setCellValue(new HSSFRichTextString("查询时间"));

        HSSFRow row1 = sheet.createRow(1);  //第二行
        HSSFCell c77 = row1.createCell(7);
        c77.setCellValue(new HSSFRichTextString("组团社"));
        HSSFCell c8 = row1.createCell(8);
        c8.setCellValue(new HSSFRichTextString("地接社"));


        //参数说明：1：开始行 2：结束行  3：开始列 4：结束列
//比如我要合并 第二行到第四行的    第六列到第八列     sheet.addMergedRegion(new CellRangeAddress(1,3,5,7));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 8));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
        sheet.addMergedRegion(new CellRangeAddress(2, list.size() + 2, 0, 0));
        //填充数据
        for (int i = 0; i < list.size(); i++) {//行
            HSSFRow row8 = sheet.createRow(i + 2);
            HSSFCell c66 = row8.createCell(0);
            c66.setCellValue(new HSSFRichTextString((String) list.get(i).get("unitName")));
            HSSFCell c661 = row8.createCell(1);
            c661.setCellValue(new HSSFRichTextString(list.get(i).get("ztagency").toString()));
            HSSFCell c662 = row8.createCell(2);
            c662.setCellValue(new HSSFRichTextString(list.get(i).get("name").toString()));
            HSSFCell c663 = row8.createCell(3);
            c663.setCellValue(new HSSFRichTextString(list.get(i).get("lifeTime").toString()));
            HSSFCell c664 = row8.createCell(4);
            c664.setCellValue(new HSSFRichTextString(list.get(i).get("teamNum").toString()));
            HSSFCell c665 = row8.createCell(5);
            c665.setCellValue(new HSSFRichTextString(list.get(i).get("types").toString()));
            HSSFCell c66s = row8.createCell(6);
            c66s.setCellValue(new HSSFRichTextString(list.get(i).get("totalMoney").toString()));
            HSSFCell c667 = row8.createCell(7);
            c667.setCellValue(new HSSFRichTextString(list.get(i).get("ztsMoney").toString()));
            HSSFCell c668 = row8.createCell(8);
            c668.setCellValue(new HSSFRichTextString(list.get(i).get("djsMoney").toString()));
            HSSFCell c669 = row8.createCell(9);
            c669.setCellValue(new HSSFRichTextString(list.get(i).get("chaxuntime").toString()));
        }

        //给全部有数据的单元格设置样式
        int rowNum = sheet.getLastRowNum();//获得总行数
        int coloumNum = sheet.getRow(1).getPhysicalNumberOfCells();
        for (int i = 0; i <= rowNum; i++) {//行
            for (int j = 0; j <= 9; j++) {//列
                sheet.setColumnWidth(j, 22 * 256);
                HSSFRow tempRow = sheet.getRow(i);
                if (i == 0) {
                    tempRow.setHeightInPoints(20);
                } else if (i == 5) {
                    tempRow.setHeightInPoints(20);
                } else {
                    tempRow.setHeightInPoints(20);
                }
                HSSFCell cell_temp = tempRow.getCell(j);
                if (cell_temp == null) {
                    cell_temp = tempRow.createCell(j);
                    cell_temp.setCellStyle(style);
                } else {
                    cell_temp.setCellStyle(style);
                }
            }
        }
        workbook.write(os);
        workbook.close();
        os.flush();
        os.close();
    }
}

